package net.etfbl.jgroky.jdbc;

import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ConnectionPool {
    
  public static ConnectionPool getConnectionPool() {
    return connectionPool;
  }
  
  public static void setConnectionPoolProperties(java.util.Properties properties) {
     try {
      connectionPool = new ConnectionPool(properties);
    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }
  
  private static ConnectionPool connectionPool;

  static {
    ResourceBundle bundle = PropertyResourceBundle.getBundle("net.etfbl.jgroky.jdbc.ConnectionPool");
    String driver = bundle.getString("driver");
    String jdbcURL = bundle.getString("jdbcURL");
    String username = bundle.getString("username");
    String password = bundle.getString("password");
    int preconnectCount = 0;
    int maxIdleConnections = 10;
    int maxConnections = 10;
    try {
      preconnectCount = Integer.parseInt(bundle.getString("preconnectCount"));
      maxIdleConnections = Integer.parseInt(bundle.getString("maxIdleConnections"));
      maxConnections = Integer.parseInt(bundle.getString("maxConnections"));
    } catch (Exception ex) {
      ex.printStackTrace();
    }
    try {
      connectionPool = new ConnectionPool(driver,
        jdbcURL, username, password,
        preconnectCount, maxIdleConnections,
        maxConnections);
    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }

  protected ConnectionPool(String aDriver,
    String aJdbcURL, String aUsername,
    String aPassword, int aPreconnectCount,
    int aMaxIdleConnections,
    int aMaxConnections)
    throws ClassNotFoundException, SQLException {

    freeConnections = new ArrayList<Connection>();
    usedConnections = new ArrayList<Connection>();
    driver = aDriver;
    jdbcURL = aJdbcURL;
    username = aUsername;
    password = aPassword;
    preconnectCount = aPreconnectCount;
    maxIdleConnections = aMaxIdleConnections;
    maxConnections = aMaxConnections;

    Class.forName(driver);
    for (int i = 0; i < preconnectCount; i++) {
      Connection conn = DriverManager.getConnection(
        jdbcURL, username, password);
      conn.setAutoCommit(true);
      freeConnections.add(conn);
    }
    connectCount = preconnectCount;
    
    // info
    //info();
  }
  
  protected ConnectionPool(java.util.Properties properties)
    throws ClassNotFoundException, SQLException {

    freeConnections = new ArrayList<Connection>();
    usedConnections = new ArrayList<Connection>();
    databaseProducName = properties.getProperty("databaseProducName");
    driver = properties.getProperty("driver");
    jdbcURL = properties.getProperty("jdbcURL")+databaseProducName;
    username = properties.getProperty("user");
    password = properties.getProperty("password");
    preconnectCount = Integer.parseInt(properties.getProperty("preconnectCount"));
    maxIdleConnections =Integer.parseInt(properties.getProperty("preconnectCount"));
    maxConnections = Integer.parseInt(properties.getProperty("preconnectCount"));

    Class.forName(driver);
    for (int i = 0; i < preconnectCount; i++) {
      Connection conn = DriverManager.getConnection(
        jdbcURL, username, password);
      conn.setAutoCommit(true);
      freeConnections.add(conn);
    }
    connectCount = preconnectCount;
    
    // info
    //info();
  }

  public synchronized Connection checkOut()
    throws SQLException {

    Connection conn = null;
    if (freeConnections.size() > 0) {
      conn = (Connection)freeConnections.get(0);
      freeConnections.remove(0);
      usedConnections.add(conn);
    } else {
      if (connectCount < maxConnections) {
        conn = DriverManager.getConnection(
          jdbcURL, username, password);
        usedConnections.add(conn);
        connectCount++;
      } else {
        try {
          wait();
          conn = (Connection)freeConnections.get(0);
          freeConnections.remove(0);
          usedConnections.add(conn);
        } catch (InterruptedException ex) {
          ex.printStackTrace();
        }
      }
    }
    return conn;
  }

  public synchronized void checkIn(Connection aConn) {
    if (aConn ==  null)
      return;
    if (usedConnections.remove(aConn)) {
      freeConnections.add(aConn);
      while (freeConnections.size() > maxIdleConnections) {
        int lastOne = freeConnections.size() - 1;
        Connection conn = (Connection)
          freeConnections.get(lastOne);
        try { conn.close(); } catch (SQLException ex) { }
        freeConnections.remove(lastOne);
      }
      notify();
    }
    
  }
  
    public String getDatabaseProducName() {
        return databaseProducName;
    }
  
    private void info() {
        try {
            System.out.println("#########################################");

            // Get the MetaData 
            Connection conn = checkOut();
            DatabaseMetaData metaData = conn.getMetaData();

            // Get driver information 
            System.out.println("");
            System.out.println("#########################################");
            System.out.println("# ***DRIVER INFORMATION***");
            System.out.println("#");
            System.out.println("# Driver Name = " + metaData.getDriverName());
            System.out.println("# Driver Version = " + metaData.getDriverVersion());
            System.out.println("#");
            System.out.println("#########################################");
            System.out.println("");
            System.out.println("");

            // Get database information 
            System.out.println("#########################################");
            System.out.println("# ***DATABASE INFORMATION***");
            System.out.println("#");
            databaseProducName = metaData.getDatabaseProductName();
            System.out.println("# Database Product Name = " + metaData.getDatabaseProductName());
            System.out.println("# Database Product Version = " + metaData.getDatabaseProductVersion());
            System.out.println("#");
            System.out.println("#########################################");
            System.out.println("");
            System.out.println("");

            // Get schema information 
            ResultSet schemas = metaData.getSchemas();
            System.out.println("#########################################");
            System.out.println("# ***SCHEMA INFORMATION***");
            System.out.println("#");
            System.out.println("# Schemas:");
            while (schemas.next()) {
                System.out.println("# " + schemas.getString(1));
            }
            System.out.println("#########################################");
            System.out.println("");
            System.out.println("");

            // Get table information 
            System.out.println("Tables");
            ResultSet tables = metaData.getTables("", "", "", null);
            while (tables.next()) {
                System.out.println(tables.getString(3));
            }
            
            checkIn(conn);
            
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionPool.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

  private String driver;
  private String jdbcURL;
  private String username;
  private String password;
  private String databaseProducName;
  private int preconnectCount;
  private int connectCount;
  private int maxIdleConnections;
  private int maxConnections;
  private ArrayList<Connection> usedConnections;
  private ArrayList<Connection> freeConnections;

}
